﻿using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Diagnostics;

namespace Citi.Helpers
{
    public class ExcelNpoiHelper
    {
        public static DataTable GetData(string filePath, string sheetName = null, bool ignoreNullRows = false, int ignoreFirstLines = 0, int ignoreFirstColumns = 0)
        {
            IWorkbook workbook = GetWorkbook(filePath);

            var sheet = sheetName == null ? workbook.GetSheetAt(0) : workbook.GetSheet(sheetName);
            var headerRow = sheet.GetRow(ignoreFirstLines);
            var firstDataRow = sheet.GetRow(ignoreFirstLines + 1);
            int cellCount = headerRow.LastCellNum;

            var table = new DataTable();

            var cellTypes = new List<CellType>[cellCount - ignoreFirstColumns];

            for (var i = ignoreFirstLines + 1; i <= sheet.LastRowNum; i++)
            {
                var row = sheet.GetRow(i);

                if (row == null) continue;

                for (int j = ignoreFirstColumns; j < cellCount - 1; j++)
                {
                    if (j >= row.Cells.Count) continue;

                    var types = cellTypes[j - ignoreFirstColumns];

                    if (types == null)
                        types = new List<CellType>();

                    if (types.Any(x => x == row.GetCell(j).CellType))
                        continue;

                    types.Add(row.Cells[j].CellType);
                }
            }

            for (int i = ignoreFirstColumns; i < cellCount; i++)//headerRow.FirstCellNum
            {
                var columnName = headerRow.GetCell(i).StringCellValue;
                var countColumns = table.Columns.Cast<DataColumn>().Count(x => x.ColumnName.StartsWith(columnName));

                if (countColumns > 0)
                    columnName += countColumns;

                try
                {
                    var types = cellTypes[i - ignoreFirstColumns];

                    if (types == null || types.Count > 1)
                        table.Columns.Add(new DataColumn(columnName, typeof(string)));
                    else
                    {
                        var type = types.First();
                        table.Columns.Add(new DataColumn(columnName,
                              type == CellType.Numeric
                              ? typeof(decimal)
                              : type == CellType.Boolean
                                  ? typeof(bool)
                                  : typeof(string)));
                    }
                }
                catch
                {
                    table.Columns.Add(new DataColumn(columnName, typeof(string)));
                }
            }

            var datatable = ReadRows(ignoreFirstLines, sheet, firstDataRow, table, cellTypes);

            CloseExcelFile();

            return datatable;
        }

        private static void CloseExcelFile()
        {
            var excelProcesses = Process.GetProcessesByName("excel").ToList();
            excelProcesses.ForEach(x => x.Kill());
        }

        private static DataTable ReadRows(int ignoreFirstLines, ISheet sheet, IRow firstDataRow, DataTable table, List<CellType>[] cellTypes)
        {
            for (var i = ignoreFirstLines + 1; i <= sheet.LastRowNum; i++)
            {
                var row = sheet.GetRow(i);
                if (row == null || row.Cells.Where(x => string.IsNullOrWhiteSpace(x.ToString())).Count() > row.Cells.Count / 1.5) return table;
                //|| row.Cells.Count < firstDataRow.Cells.Count
                var dataRow = table.NewRow();

                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (row.GetCell(j + row.FirstCellNum) != null)
                    {
                        var value = row.GetCell(j + row.FirstCellNum);

                        if (firstDataRow.GetCell(j + row.FirstCellNum) == null)
                        {
                            dataRow[j] = value != null ? value.ToString() : null;
                            continue;
                        }

                        var types = cellTypes[j + row.FirstCellNum];

                        if (types == null || types.Count > 1)
                        {
                            dataRow[j] = value != null ? value.ToString() : null;
                            continue;
                        }

                        switch (types.First())
                        {
                            case CellType.Numeric:
                                dataRow[j] = row.GetCell(j + row.FirstCellNum).NumericCellValue;
                                break;
                            case CellType.Boolean:
                                dataRow[j] = row.GetCell(j + row.FirstCellNum).BooleanCellValue;
                                break;
                            default:
                                dataRow[j] = row.GetCell(j + row.FirstCellNum).ToString();
                                break;
                        }

                    }
                }

                table.Rows.Add(dataRow);
            }

            return table;
        }

        public static IWorkbook GetWorkbook(string filePath)
        {
            return filePath.EndsWith("xlsx")
                ? (IWorkbook)new XSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))
                : new HSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read));
        }

        private void MergeData(string filePath, string sheetName, DataTable dataTable, bool hasHeader, int ignoredLines = 0)
        {
            IWorkbook workbook = GetWorkbook(filePath);
            var sheet = workbook.GetSheet(sheetName);
            var headerRow = (XSSFRow)sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;

            if (hasHeader && dataTable.Rows.Count == 0)
            {
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    // get data as the column header of DataTable
                    var column = new DataColumn(headerRow.GetCell(i).StringCellValue);

                    dataTable.Columns.Add(column);
                }
            }
            //else
            //{
            //    // TODO: check if the subsequent sheet corresponds

            //}

            int rowCount = sheet.LastRowNum;
            for (int i = (sheet.FirstRowNum + ignoredLines + (hasHeader ? 1 : 0)); i < rowCount; i++)
            {
                var row = (XSSFRow)sheet.GetRow(i);
                var dataRow = dataTable.NewRow();
                if (row.FirstCellNum > -1)
                {
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                }

                dataTable.Rows.Add(dataRow);
            }
            workbook = null;
            sheet = null;
        }

        public static void ExportDataTableToExcel(DataTable dtSource, string strFileName, string sheetNameToSave)
        {
            // create workbook
            var workbook = new XSSFWorkbook();
            // the table named mySheet
            var sheet = (XSSFSheet)workbook.CreateSheet(sheetNameToSave);
            // create the first row
            var dataRow = (XSSFRow)sheet.CreateRow(0);
            foreach (DataColumn column in dtSource.Columns)
                dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            //create rows on the basis of data from datatable(not including table header), and add data into cells in every row
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                dataRow = (XSSFRow)sheet.CreateRow(i + 1);
                for (int j = 0; j < dtSource.Columns.Count; j++)
                    dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
            }
            using (var ms = new MemoryStream())
            {
                using (var fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
                    workbook.Write(fs);
            }
        }
    }
}